package org.goldenport.g3.adapters

import java.util.Properties
import java.sql._
import com.asamioffice.goldenport.text.UString.{notNull => not_null}
import org.goldenport.g3._
import org.goldenport.g3.messages.{Record, RecordSet}
import org.goldenport.g3.messages.sql._
import org.goldenport.g3.messages.sql.SqlUtil.{symbol2name => to_name, value2literal => to_literal}
import org.goldenport.g3.events.sql._

/**
 * @since   Apr. 11, 2010
 * @version Jun. 26, 2011
 * @author  ASAMI, Tomoharu
 */
class SqlAdapter(val uri: String = "",
                 val username: String = "", val password: String = "",
                 val driver: String = ""
               ) extends G3Adapter with JdbcParameters {
  private lazy val _uri = jdbc_uri(uri)
  private lazy val sql_driver = new SqlDriver(_uri, username, password, driver)

  override def open_Adapter() {
    sql_driver.open(context)
  }

  override def close_Adapter() {
    sql_driver.close()
  }

  override protected def do_Action(content: AnyRef, parcel: G3Parcel, ctx: G3AgentContext): AnyRef = {
//    println("Content = " + content)
    content match {
      case sql: SqlCommand => sql_driver.execute(sql, ctx)
    }
/*
    content match {
      case create: CreateTable => sql_driver.create(_uri, create, ctx)
      case read: ReadTable => sql_driver.read(_uri, read, ctx)
        //      case select: SelectTables => sql_driver.select(_uri, select, ctx)
      case insert: InsertTable => sql_driver.insert(_uri, insert, ctx)
      case update: UpdateTable => sql_driver.update(_uri, update, ctx)
      case delete: DeleteTable => sql_driver.delete(_uri, delete, ctx)
      case drop: DropTable => sql_driver.drop(_uri, drop, ctx)
    }
*/
  }
}

abstract class DatatypeFactory {
  def getTypeName(datatype: SqlDatatype) = {
    datatype.fullName
  }

  def getConstraintName(constraint: SqlConstraint) = {
    constraint.fullName
  }
}

class DerbyDatatypeFactory extends DatatypeFactory {
  override def getConstraintName(constraint: SqlConstraint) = {
    constraint match {
      case AUTO_INCREMENT => "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"
      case _ => constraint.fullName
    }
  }
}

class SqlDriver(uri: String = "", username: String = "", password: String = "", driver: String = "") extends G3Contextable with JdbcParameters {
  private lazy val _driver = jdbc_driver(driver)
  private lazy val _uri = jdbc_uri(uri)
  private lazy val _username = jdbc_username(username)
  private lazy val _password = jdbc_password(password)

  def open(ctx: G3Context) {
    setContext(ctx)
//  Class.forName(_driver)
  }

  def close() {
  }

  protected def new_Agent(ctx: G3AgentContext) = {
    new PlainSqlDriverAgent(_uri, _username, _password, _driver, ctx)
  }

  def execute(cmd: SqlCommand, context: G3AgentContext): G3Event = {
    val agent = new_Agent(context)
    agent.open()
    try {
      val evt = agent.execute(cmd)
      evt.inputMessage = Some(cmd)
      evt
    } finally {
      agent.close()
    }
  }

/*
  def create(msg: SqlCreate, context: G3AgentContext): G3Event = {
    val agent = new_Agent("", context)
    agent.open()
    try {
      val evt = agent.create(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  def select(uri: String, msg: Select, context: G3AgentContext): G3Event = {
    val agent = new_Agent(uri, context)
    agent.open()
    try {
      val evt = agent.select(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  def insert(uri: String, msg: InsertTable, context: G3AgentContext): G3Event = {
    val agent = new_Agent(uri, context)
    agent.open()
    try {
      val evt = agent.insert(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  def update(uri: String, msg: UpdateTable, context: G3AgentContext): G3Event = {
    val agent = new_Agent(uri, context)
    agent.open()
    try {
      val evt = agent.update(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  def delete(uri: String, msg: DeleteTable, context: G3AgentContext): G3Event = {
    val agent = new_Agent(uri, context)
    agent.open()
    try {
      val evt = agent.delete(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  def drop(msg: DropTable, context: G3AgentContext): G3Event = {
    val agent = new_Agent("", context)
    agent.open()
    try {
      val evt = agent.drop(msg)
      evt.inputMessage = Some(msg)
      evt
    } finally {
      agent.close()
    }
  }

  // XXX unify G3Driver
  protected final def make_output(value: AnyRef) = {
    value match {
      case None => None
      case u: Unit => None
      case _ => Some(value)
    }
  }
*/
}

class PlainSqlDriverAgent(
  val uri: String, val username: String, val password: String,
  val driver: String,
  val ctx: G3AgentContext
) extends G3Contextable {
  lazy val factory = ctx.sqlDatatypeFactory(uri)

  def open() {
    setContext(ctx.context)
  }

  def close() {
  }

  def execute(cmd: SqlCommand): G3Event = {
//    log_debug("sql execute = " + cmd)
    cmd match {
      case sql: SqlCreate => create(sql)
      case sql: SqlDrop => drop(sql)
      case sql: SqlSelect => select(sql)
      case sql: SqlInsert => insert(sql)
      case sql: SqlUpdate => update(sql)
      case sql: SqlDelete => delete(sql)
    }
  }

  def create(sql: SqlCreate): G3Event = {
    def make_statement(sql: SqlCreate) = {
      "CREATE TABLE " + sql.tableName + " " +
      (for (column <- sql.ddl.columns) yield {
        to_name(column.name) + " " + factory.getTypeName(column.datatype) +
        (column.constraints.map(factory.getConstraintName) match {
          case Nil => ""
          case list => " " + list.mkString(" ")
        })
      }).mkString("(", ", ", ")")
    }

    val statement = make_statement(sql)
    log_debug("SQL Create = " + statement)
    try {
      val conn = get_connection()
      conn.createStatement().execute(statement)
      new SqlCreated(get_uri(sql.tableName), sql, statement)
    } catch {
      case e: Exception => {
//        log_debug("sqlcreate = " + e)
        new SqlExceptioned(get_uri(sql.tableName), sql, statement, e)
      }
    }
  }

  private def get_connection() = {
    ctx.getJdbcConnection(uri, username, password, driver)
  }

/* 2010-09-24
  private def get_connection0() = {
    log_debug("sql = " + uri + ", " + username + ", " + password)
    val props = new Properties()
    props.put("user", username)
    props.put("password", password)
    DriverManager.getConnection(uri, props)
//    conn.setAutoCommit(false)
  }
*/

  def select(sql: SqlSelect): G3Event = {
    val name = uri
    val query = sql.statement
    log_debug("SQL Query = " + query)
    try {
      val conn = get_connection()
      val rs = conn.createStatement().executeQuery(query)
      context.openResultSet(rs)
      new SqlSelected(name, sql, RecordSet(rs, context))
    } catch {
      case e: Exception => {
        new SqlExceptioned(name, sql, query, e)
      }
    }
  }

  def insert(sql: SqlInsert): G3Event = {
    def make_statement(record: Record) = {
      "INSERT INTO %s %s VALUES %s".format(
        sql.tableName,
        record.sqlFieldsClause,
        record.sqlValuesClause
      )
    }

    val name = get_uri(sql.tableName)
    try {
      val conn = get_connection()
      val st = conn.createStatement()
      for (record <- sql.records) {
        println("sql insert record = " + record)
        log_debug("SQL Insert = " + make_statement(record))
        st.execute(make_statement(record))
      }
      new SqlInserted(name, sql)
    } catch {
      case e: Exception => {
        new SqlExceptioned(name, sql, "", e)
      }
    }
  }

  def update(sql: SqlUpdate): G3Event = {
    val name = get_uri(sql.tableName)
    try {
      val conn = get_connection()
      val st = conn.createStatement()
      for (statement <- sql.statements) {
        log_debug("SQL Update = " + statement)
        st.execute(statement)
      }
      new SqlUpdated(name, sql)
    } catch {
      case e: Exception => {
        new SqlExceptioned(name, sql, "", e)
      }
    }
  }

  // "-" | "_" | "." | "!" | "~" | "*" | "'" | "(" | ")"
  def delete(sql: SqlDelete): G3Event = {
    def make_statement = sql.statement

    val name = {
      sql.uris match {
        case Nil => uri
        case names => uri + "/table=" + names.mkString(".")
      }
    }
    try {
      val conn = get_connection()
      val st = conn.createStatement()
      log_debug("SQL Delete = " + make_statement)
      st.execute(make_statement)
      new SqlDeleted(name, sql)
    } catch {
      case e: Exception => {
        new SqlExceptioned(name, sql, "", e)
      }
    }
  }

  def drop(sql: SqlDrop): G3Event = {
    val name = get_uri(sql.tableName)
    execute_statement(name, sql) {
      "DROP TABLE " + sql.tableName
    } { statement =>
      new SqlDropped(name, sql, statement)
    }
  }

  private def get_uri(tableName: String) = {
    uri + "/" + tableName
  }

  private def execute_statement(name: String, sql: SqlCommand)(makeStatement: => String)(makeEvent: String => G3Event): G3Event = {
    val statement = makeStatement
    try {
      val conn = get_connection()
      log_debug("SQL = " + statement)
      conn.createStatement().execute(statement)
      makeEvent(statement)
    } catch {
      case e: Exception => {
//        e.printStackTrace()
        new SqlExceptioned(name, sql, statement, e)
      }
    }
  }
}

trait JdbcParameters extends G3Contextable {
  protected final def jdbc_driver(driver: String) = {
    if (not_null(driver)) driver else context.getDefaultJdbcDriver
  }

  protected final def jdbc_uri(uri: String) = {
    if (not_null(uri)) uri else context.getDefaultJdbcUrl
  }

  protected final def jdbc_username(username: String) = {
    if (not_null(username)) username
    else context.getAuthentication("jdbc://") match {
      case Some(auth: BasicAuthentication) => auth.user
      case None => context.getDefaultJdbcUsername
    }
  }

  protected final def jdbc_password(password: String) = {
    if (not_null(password)) password
    else context.getAuthentication("jdbc://") match {
      case Some(auth: BasicAuthentication) => auth.password
      case None => context.getDefaultJdbcPassword
    }
  }
}
